IF OBJECT_ID('ssf.job_get_run_step_status_info') IS NOT NULL
	DROP FUNCTION ssf.job_get_run_step_status_info
GO

CREATE FUNCTION ssf.job_get_run_step_status_info (
	@job_id UNIQUEIDENTIFIER
	, @run_datetime DATETIME
)
RETURNS TABLE
AS
/*
<object_header>

<object_summary>
	<purpose><![CDATA[
Return step info from sysjobhistory and sysjobsteps for the given job_id and run date/time.
	]]></purpose>
</object_summary>

</object_header>
*/

	RETURN (
		SELECT h.instance_id
			, @job_id AS job_id
			, h.step_id
			, h.step_name
			, s.subsystem
			, s.command
			, s.database_name
			, h.[message] AS step_message
			, h.run_status AS step_run_status
			, h.step_run_datetime
			, ssf.job_run_duration_ss(h.run_duration) AS step_run_duration_ss
		FROM (
			SELECT instance_id
				, job_id
				, step_id
				, step_name
				, [message]
				, run_status
				, ssf.job_run_datetime(run_date, run_time) AS step_run_datetime
				, run_duration
			FROM msdb.dbo.sysjobhistory WITH (NOLOCK)
			WHERE job_id = @job_id
			AND ssf.job_run_datetime(run_date, run_time) >= @run_datetime
		) h
		JOIN msdb.dbo.sysjobsteps s WITH (NOLOCK)
		ON h.job_id = s.job_id
		AND h.step_id = s.step_id
		WHERE h.step_run_datetime <= (
			SELECT DATEADD(SECOND, run_duration_ss, run_datetime) FROM ssf.job_get_run_status_info(@job_id, @run_datetime)
		)
	)
GO
